const express = require("express");
const router = express.Router();
const { User, sequelize } = require("../../models");
const { success, failure } = require("../../utils/response");
const { NotFound } = require("http-errors");

/**
 * 统计用户性别
 */
router.get("/sex", async (req, res) => {
  try {
    const male = await User.count({ where: { sex: 0 } });
    const female = await User.count({ where: { sex: 1 } });
    const unknown = await User.count({ where: { sex: 2 } });

    const data = [
      { value: male, name: "男性" },
      { value: female, name: "女性" },
      { value: unknown, name: "未选择" },
    ];

    success(res, "查询用户性别成功", { data });
  } catch (error) {
    failure(res, error);
  }
});

router.get("/promise/sex", async (req, res) => {
  try {
    const [male, female, unknown] = await Promise.all([
      User.count({ where: { sex: 0 } }),
      User.count({ where: { sex: 1 } }),
      User.count({ where: { sex: 2 } }),
    ]);

    const data = [
      { value: male, name: "男性" },
      { value: female, name: "女性" },
      { value: unknown, name: "未选择" },
    ];
    success(res, "查询用户性别成功", { data });
  } catch (error) {
    failure(res, error);
  }
});

/**
 * 统计用户每月注册人数
 */
router.get("/user", async (req, res) => {
  try {
    // orm查询方式
    // const result = await User.findAll({
    //     attributes: [
    //         [sequelize.fn('DATE_FORMAT', sequelize.col('createdAt'), '%Y-%m'), 'month'],
    //         [sequelize.fn('COUNT', sequelize.col('id')), 'value']
    //     ],
    //     group: ['month'],
    //     order: [['month','ASC']],
    //     raw: true
    // })

    // 原生sql查询方式
    const [result] = await sequelize.query(
      "select DATE_FORMAT(createdAt,'%Y-%m') as month,count(*) as value from users GROUP BY month ORDER BY month DESC;"
    );

    const data = {
      months: [],
      values: [],
    };

    result.forEach((item) => {
      data.months.push(item.month);
      data.values.push(item.value);
    });

    success(res, "查询用户注册人数成功", { data });
  } catch (error) {
    failure(res, error);
  }
});

module.exports = router;
